
package com.feib.soeasy.service.impl;

import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.text.DecimalFormat;
import java.text.NumberFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.UUID;

import javax.annotation.Resource;

import org.apache.commons.io.FileUtils;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.PrintSetup;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Service;
import org.springframework.util.LinkedCaseInsensitiveMap;

import com.feib.soeasy.dao.GroupDao;
import com.feib.soeasy.dao.PaymentDao;
import com.feib.soeasy.dao.RealAccountDao;
import com.feib.soeasy.dao.Soez5005Dao;
import com.feib.soeasy.dao.WriteOffPaymentDao;
import com.feib.soeasy.model.CommissionCodeTable;
import com.feib.soeasy.model.Group;
import com.feib.soeasy.model.Payment;
import com.feib.soeasy.model.RealAccount;
import com.feib.soeasy.model.WriteOffPayment;
import com.feib.soeasy.service.Soez5005Service;
import com.feib.soeasy.util.DateUtil;
import com.feib.soeasy.util.UserUtil;


/**
 * @title (#)Soez5005ServiceImpl.java<br>
 * @description <br>
 * @author Jimmy Liu<br>
 * @version 1.0.0 2010/11/25
 * @copyright Far Eastern International Bank Copyright (c) 2010<br>
 * @2011/3/14 create by Jimmy Liu<br>
 */
@Service("soez5005Service")
public class Soez5005ServiceImpl extends BaseServiceImpl implements Soez5005Service {

	private static NumberFormat numberFormat = new DecimalFormat("##,###");
	protected Logger logger = LoggerFactory.getLogger(this.getClass());



	@Resource(name="soez5005Dao")
	private Soez5005Dao soez5005Dao;

	@Resource(name="groupDao")
	private GroupDao groupDao;

	@Resource(name="paymentDao")
	private PaymentDao paymentDao;

	@Resource(name="realAccountDao")
	private RealAccountDao realAccountDao;

	@Resource(name="writeOffPaymentDao")
	private WriteOffPaymentDao writeOffPaymentDao;



	/**
	 * 暫存目錄
	 */
	@Resource(name = "tempDirectory")
	private String tempDirectory;




	protected Map<String, List<Map<String, Object>> > preProcessGroup(List<Map<String, Object>> dbRecords){

		Map<String, List<Map<String, Object>> > result = new LinkedCaseInsensitiveMap<List<Map<String,Object>>>();
		Map<String, Map<String, Object>> groupPkSubTotals = new LinkedCaseInsensitiveMap<Map<String, Object>>();

		for(Map<String, Object> dbRecord : dbRecords )
		{

			//select   group_pk, GROUP_NAME  ,VIRTUAL_ACCOUNT_NO , ACCOUNT_NO ,
			String groupPk = String.valueOf(dbRecord.get("GROUP_PK"));//String.valueOf(dbRecord.get("group_pk")
			//Long groupPk = new Long(String.valueOf(dbRecord.get("group_pk")));
			String groupName = (String)dbRecord.get("GROUP_NAME");
			String VirtualAccountNo = (String)dbRecord.get("VIRTUAL_ACCOUNT_NO");
			String AccountNo = (String)dbRecord.get("ACCOUNT_NO");
			//  String commissionCodeName = (String)dbRecord.get("COMMISSION_CODE_NAME");
			Long recordCount = new Long(String.valueOf(dbRecord.get("RECORD_COUNT")));
			//Long amount = new Long(String.valueOf(dbRecord.get("AMOUNT")));
			//Long commissionAmount = new Long(String.valueOf(dbRecord.get("COMMISSION_AMOUNT")));
			//Long bankAmount = new Long(String.valueOf(dbRecord.get("BANK_AMOUNT")));
			Long realAmount = new Long(String.valueOf(dbRecord.get("REAL_AMOUNT")));

			// 依據通路歸類資料
			List<Map<String, Object>> data = result.get(groupPk);
			if (null == data)
			{
				data = new ArrayList<Map<String,Object>>();
				result.put(groupPk, data);                
			}

			Map<String, Object> toAddInData = new LinkedCaseInsensitiveMap<Object>();
			toAddInData.put("GROUP_PK", groupPk);
			toAddInData.put("GROUP_NAME",groupName );
			toAddInData.put("VIRTUAL_ACCOUNT_NO", VirtualAccountNo);
			toAddInData.put("ACCOUNT_NO", AccountNo);
			toAddInData.put("RECORD_COUNT", recordCount);
			toAddInData.put("REAL_AMOUNT", realAmount);

			data.add(toAddInData);

			// 累計
			Map<String, Object> groupPkSubTotal = groupPkSubTotals.get(groupPk);
			if (null == groupPkSubTotal)
			{
				groupPkSubTotal = new LinkedCaseInsensitiveMap<Object>();
				groupPkSubTotal.put("GROUP_PK", groupPk);
				groupPkSubTotal.put("GROUP_NAME",groupName );
				groupPkSubTotal.put("VIRTUAL_ACCOUNT_NO", "小計");

				groupPkSubTotal.put("ACCOUNT_NO", "");

				groupPkSubTotal.put("RECORD_COUNT", recordCount);        
				groupPkSubTotal.put("REAL_AMOUNT", realAmount);


				groupPkSubTotals.put(groupPk, groupPkSubTotal);
			}
			else
			{
				groupPkSubTotal.put("RECORD_COUNT", (Long)groupPkSubTotal.get("RECORD_COUNT") + recordCount);           
				groupPkSubTotal.put("REAL_AMOUNT", (Long)groupPkSubTotal.get("REAL_AMOUNT") + realAmount);
			}
		}

		// 將個別的累計放在個別資料集合的最後一筆資料
		Iterator<String> groupPkIt = result.keySet().iterator();
		while(groupPkIt.hasNext())
		{
			String groupPk = groupPkIt.next();
			result.get(groupPk).add(groupPkSubTotals.get(groupPk));            
		}

		groupPkSubTotals.clear();

		return result;

	} 


	public Map<String, List<Map<String, Object>>> queryForGroupStatisticsList(Group group, Integer qryDateType, Date dateFrom, Date dateTo) {

		if (null != dateFrom)
		{
			// 將時分秒設定在00:00:00
			dateFrom = DateUtil.getBeginOfDateTime(dateFrom);
		}

		if (null != dateTo)
		{
			// 將時分秒設定在23:59:59
			dateTo = DateUtil.getEndOfDateTime(dateTo);
		}

		//2011/06/16 jeff Gu  start   修正    業者代號不存在時 查詢錯誤問題

		Group dbGroup = null;
		if (null != group && StringUtils.isNotEmpty(group.getGroupNo()))
		{  
			dbGroup = groupDao.findByGroupNo(group.getGroupNo());
			group.setGroupPk(dbGroup.getGroupPk());
			group.setGroupName(dbGroup.getGroupName());
		}
		// WriteOffPayment.CHANNEL_7_11getStatisticsForGroup


		/*
		 * 2011.12 by Leo  更改
		 * 入帳金額=代收金額-代收手續費-帳務處理費 
		 * 代收金額=超商+郵局+銀行+自行繳款
		 * 代收手續費=超商手續費內含時(例：AFF*10筆=100元)帳單業者負擔
              			超商手續費外加時(例：AFF*10筆=0元)繳款者負擔
		 * 帳務處理費=各別通路之帳務處理費*筆數
		 * groupPk 業者pk
		 * groupName 業者名稱　
		 * virtualAccountNo 虛擬帳號
		 * accountNo 實體帳號
		 * recordCount 代收筆數
		 * realAmount 入帳金額
		 */	
		List<Map<String, Object>> dbRecords = new ArrayList<Map<String,Object>>();
		Long groupPk = group.getGroupPk();
		String groupName = group.getGroupName();
		String virtualAccountNo;
		String accountNo;
		Integer recordCount = new Integer(0);
		Long realAmount = new Long(0);

		RealAccount qryRealAccount = new RealAccount();
		qryRealAccount.setGroup(group);

		List realAccounts = realAccountDao.queryByGroup(qryRealAccount);      

		//queryPaymentByGroup(Group group, Integer qryDateType, Date qryDateFrom, Date qryDateTo)
		List payments = paymentDao.queryPaymentByGroup(group, qryDateType, dateFrom, dateTo);
		if(realAccounts != null && realAccounts.size() > 0)
		{        	
			if(payments != null && payments.size() >0 )
			{
				for(Iterator<RealAccount> r=realAccounts.iterator();r.hasNext();)
				{
					RealAccount ra = (RealAccount)r.next();
					virtualAccountNo = ra.getVirtualAccountNo();
					accountNo = ra.getAccountNo();

					//初始化
					Long totalAmount = new Long(0);
					realAmount = new Long(0);
					recordCount = new Integer(0);
					Long handleAmount = new Long(0); // 帳務處理費
					Long groupAmount = new Long(0); // 代收手續費

					for(Iterator<Payment> i = payments.iterator();i.hasNext();)
					{
						Payment p = (Payment)i.next();
						//虛擬帳戶與Payment 的虛擬帳戶前幾碼相同者為同一群組
						if(p.getVirtualAccount().substring(0,ra.getVirtualAccountNo().length()).equals(ra.getVirtualAccountNo()))
						{
							//算出這個帳戶的總筆數及總代收金額
							//代收金額=超商+郵局+銀行+自行繳款

							recordCount = recordCount + 1;
							totalAmount = totalAmount + p.getTotalAmount();

							//計算內含/外加 - 代收手續費
							WriteOffPayment w = p.getWriteOffPayment();

							// 超商與郵局才收手續費
							if(w.getChannel().equals("D") || w.getChannel().equals("E") ||
									w.getChannel().equals("F") || w.getChannel().equals("G"))
							{
								// 超商
								Set commisionCodes = p.getUploadFile().getFormatDefinition().getCommissionCodeTables();
								String commissionCode = "";
								if(p.getPaymentStatus().equals(Payment.PAYMENT_STATUS_CLOSED))
								{
									if(w.getAmount().equals(p.getTotalLoanableAmount()))
									{
										commissionCode = p.getBarcodeStores1Loanable().substring(6);
									}
									else
									{
										commissionCode = p.getBarcodeStores1().substring(6);
									}
								}
								else
								{
									commissionCode = p.getBarcodeStores1().substring(6);
								}

								for(Iterator<CommissionCodeTable> c = commisionCodes.iterator();c.hasNext();)
								{
									CommissionCodeTable cct = (CommissionCodeTable)c.next();
									if(commissionCode.equals(cct.getCommissionCode()))
									{
										//if(cct.getCommissionType().equals("I"))
										//{
										groupAmount = groupAmount + cct.getGroupAmount(); //直接抓取 groupAmount
										break;
										//}
									}
								}

							}
							else if (w.getChannel().equals("H"))
							{
								// 郵局 手續費
								if (p.getTotalAmount() <= 0)
								{
									// 應該不會發生，防呆
								}
								else if (p.getTotalAmount() <= 100)
								{
									groupAmount = groupAmount + p.getGroup().getPostLevel1();
								}
								else if (p.getTotalAmount() <= 1000)
								{
									groupAmount = groupAmount + p.getGroup().getPostLevel2();
								}
								else
								{
									groupAmount = groupAmount + p.getGroup().getPostLevel3();
								}
							}




							//帳務處理費=各別通路之帳務處理費*筆數
							//超商
							if(w.getChannel().equals("D") || w.getChannel().equals("E") ||
									w.getChannel().equals("F") || w.getChannel().equals("G"))
							{
								if(p.getGroup().getChainstoreAmount() != null)
									handleAmount = handleAmount + p.getGroup().getChainstoreAmount();
							}else if(w.getChannel().equals("0"))//臨櫃
							{
								if(p.getGroup().getBankAmount() != null)
									handleAmount = handleAmount + p.getGroup().getBankAmount();
							}else if( w.getChannel().equals("H"))//郵局
							{
								if(p.getGroup().getPostAmount() != null)
									handleAmount = handleAmount + p.getGroup().getPostAmount();
							}else if(w.getChannel().equals("2") || w.getChannel().equals("3") || w.getChannel().equals("4") ) //ATM/IB
							{
								if(p.getGroup().getAtmAmount() != null)
									handleAmount = handleAmount + p.getGroup().getAtmAmount();
							}else if(w.getChannel().equals("8"))//匯款
							{
								if(p.getGroup().getRemittanceAmount() != null)
									handleAmount = handleAmount + p.getGroup().getRemittanceAmount();
							} /* 20120828 自行繳款之「帳務處理費」計算   */
							else if(w.getChannel().equals("I"))//ACH
							{
								if(p.getGroup().getOtherAmount() != null)
									handleAmount = handleAmount + p.getGroup().getOtherAmount();
							}else if(w.getChannel().equals("Z"))//自行繳款
							{
								if(p.getGroup().getSelfAmount() != null)
									handleAmount = handleAmount + p.getGroup().getSelfAmount();
							}
							else //除了超商跟郵局, 都歸在其他
							{
								if(p.getGroup().getOtherAmount() != null)
									handleAmount = handleAmount + p.getGroup().getOtherAmount();
							}

					}

				}


				if(recordCount != 0)
				{
					realAmount = totalAmount - groupAmount - handleAmount;

					Map<String, Object> map = new HashMap<String, Object>();
					map.put("GROUP_PK", groupPk);
					map.put("GROUP_NAME", groupName);
					map.put("VIRTUAL_ACCOUNT_NO", virtualAccountNo);
					map.put("ACCOUNT_NO", accountNo);
					map.put("RECORD_COUNT", recordCount);
					map.put("REAL_AMOUNT", realAmount);
					dbRecords.add(map);
				}
			}

		}	
	}


	//List<Map<String, Object>> dbRecords = soez5005Dao.getStatisticsForGroup((null==dbGroup ?null:dbGroup.getGroupPk()), null, qryDateType, dateFrom, dateTo);



	//2011/06/16 jeff Gu  end   修正    業者代號不存在時 查詢錯誤問題
	//只須超商資料
	//List<Map<String, Object>> dbRecords2 = soez5006Dao.getStatisticsForNotStore(dbGroup.getGroupPk(), null, qryDateType, dateFrom, dateTo);

	//dbRecords.addAll(dbRecords2);

	if (null == dbRecords || 0 == dbRecords.size())
		return null;

	return this.preProcessGroup(dbRecords);
}







/**
 * Create a library of cell styles
 */
private static Map<String, CellStyle> createStyles(Workbook wb){
	Map<String, CellStyle> styles = new HashMap<String, CellStyle>();
	CellStyle style;
	Font titleFont = wb.createFont();
	titleFont.setFontName("標楷體");
	titleFont.setFontHeightInPoints((short)24);
	style = wb.createCellStyle();
	style.setAlignment(CellStyle.ALIGN_CENTER);
	style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
	style.setFont(titleFont);
	styles.put("title", style);

	Font title2Font = wb.createFont();
	title2Font.setFontName("標楷體");
	//2010/05/13 jeff Gu  start   修正    報表excel檔修正 數值過長時欄位展開 問題  
	title2Font.setFontHeightInPoints((short)12);
	style = wb.createCellStyle();
	style.setFont(title2Font);      
	style.setWrapText(false);//設定文字是否換行
	//2010/05/13 jeff Gu  end   修正    報表excel檔修正 數值過長時欄位展開 問題
	style.setAlignment(CellStyle.ALIGN_LEFT);
	style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
	styles.put("title2", style);

	Font headerFont = wb.createFont();
	headerFont.setFontName("標楷體");
	headerFont.setFontHeightInPoints((short)12);
	style = wb.createCellStyle();
	style.setFont(headerFont);
	//2010/05/13 jeff Gu  start   修正    報表excel檔修正 數值過長時欄位展開 問題  
	style.setWrapText(false);//設定文字是否換行
	//2010/05/13 jeff Gu  end   修正    報表excel檔修正 數值過長時欄位展開 問題
	style.setAlignment(CellStyle.ALIGN_CENTER);
	style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
	style.setBorderRight(CellStyle.BORDER_THIN);
	style.setRightBorderColor(IndexedColors.BLACK.getIndex());
	style.setBorderLeft(CellStyle.BORDER_THIN);
	style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
	style.setBorderTop(CellStyle.BORDER_THIN);
	style.setTopBorderColor(IndexedColors.BLACK.getIndex());
	style.setBorderBottom(CellStyle.BORDER_THIN);
	style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
	styles.put("header", style);

	Font cellFont = wb.createFont();
	cellFont.setFontName("標楷體");
	cellFont.setFontHeightInPoints((short)12);
	style = wb.createCellStyle();
	style.setFont(cellFont);
	style.setAlignment(CellStyle.ALIGN_CENTER);
	style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
	//2010/05/13 jeff Gu  start   修正    報表excel檔修正 數值過長時欄位展開 問題  
	style.setWrapText(false);//設定文字是否換行
	//2010/05/13 jeff Gu  end   修正    報表excel檔修正 數值過長時欄位展開 問題
	style.setBorderRight(CellStyle.BORDER_THIN);
	style.setRightBorderColor(IndexedColors.BLACK.getIndex());
	style.setBorderLeft(CellStyle.BORDER_THIN);
	style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
	style.setBorderTop(CellStyle.BORDER_THIN);
	style.setTopBorderColor(IndexedColors.BLACK.getIndex());
	style.setBorderBottom(CellStyle.BORDER_THIN);
	style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
	styles.put("cell", style);

	style = wb.createCellStyle();
	Font cellNumberFont = wb.createFont();
	cellNumberFont.setFontName("Arial");
	cellNumberFont.setFontHeightInPoints((short)14);
	style.setFont(cellNumberFont);
	style.setAlignment(CellStyle.ALIGN_RIGHT);
	style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
	//2010/05/13 jeff Gu  start   修正    報表excel檔修正 數值過長時欄位展開 問題  
	style.setWrapText(false);//設定文字是否換行
	//2010/05/13 jeff Gu  end   修正    報表excel檔修正 數值過長時欄位展開 問題
	style.setBorderRight(CellStyle.BORDER_THIN);
	style.setRightBorderColor(IndexedColors.BLACK.getIndex());
	style.setBorderLeft(CellStyle.BORDER_THIN);
	style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
	style.setBorderTop(CellStyle.BORDER_THIN);
	style.setTopBorderColor(IndexedColors.BLACK.getIndex());
	style.setBorderBottom(CellStyle.BORDER_THIN);
	style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
	style.setDataFormat(wb.createDataFormat().getFormat("$#,##0"));
	styles.put("cell_number", style);

	style = wb.createCellStyle();
	Font cellDateFont = wb.createFont();
	cellDateFont.setFontName("Arial");
	cellDateFont.setFontHeightInPoints((short)12);
	style.setFont(cellDateFont);
	style.setAlignment(CellStyle.ALIGN_CENTER);
	style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
	//2010/05/13 jeff Gu  start   修正    報表excel檔修正 數值過長時欄位展開 問題  
	style.setWrapText(false);//設定文字是否換行
	//2010/05/13 jeff Gu  end   修正    報表excel檔修正 數值過長時欄位展開 問題
	style.setBorderRight(CellStyle.BORDER_THIN);
	style.setRightBorderColor(IndexedColors.BLACK.getIndex());
	style.setBorderLeft(CellStyle.BORDER_THIN);
	style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
	style.setBorderTop(CellStyle.BORDER_THIN);
	style.setTopBorderColor(IndexedColors.BLACK.getIndex());
	style.setBorderBottom(CellStyle.BORDER_THIN);
	style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
	style.setDataFormat(wb.createDataFormat().getFormat("yyyy/MM/dd"));
	styles.put("cell_date", style);


	cellFont = wb.createFont();
	cellFont.setFontName("標楷體");
	cellFont.setFontHeightInPoints((short)18);
	cellFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
	style = wb.createCellStyle();
	style.setFont(cellFont);
	style.setAlignment(CellStyle.ALIGN_CENTER);
	style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
	//2010/05/13 jeff Gu  start   修正    報表excel檔修正 數值過長時欄位展開 問題  
	style.setWrapText(false);//設定文字是否換行
	//2010/05/13 jeff Gu  end   修正    報表excel檔修正 數值過長時欄位展開 問題
	style.setBorderRight(CellStyle.BORDER_THIN);
	style.setRightBorderColor(IndexedColors.BLACK.getIndex());
	style.setBorderLeft(CellStyle.BORDER_THIN);
	style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
	style.setBorderTop(CellStyle.BORDER_THIN);
	style.setTopBorderColor(IndexedColors.BLACK.getIndex());
	style.setBorderBottom(CellStyle.BORDER_THIN);
	style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
	styles.put("cell_sub_total", style);

	style = wb.createCellStyle();
	cellNumberFont = wb.createFont();
	cellNumberFont.setFontName("Arial");
	cellNumberFont.setFontHeightInPoints((short)14);
	cellNumberFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
	style.setFont(cellNumberFont);
	style.setAlignment(CellStyle.ALIGN_RIGHT);
	style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
	//2010/05/13 jeff Gu  start   修正    報表excel檔修正 數值過長時欄位展開 問題  
	style.setWrapText(false);//設定文字是否換行
	//2010/05/13 jeff Gu  end   修正    報表excel檔修正 數值過長時欄位展開 問題
	style.setBorderRight(CellStyle.BORDER_THIN);
	style.setRightBorderColor(IndexedColors.BLACK.getIndex());
	style.setBorderLeft(CellStyle.BORDER_THIN);
	style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
	style.setBorderTop(CellStyle.BORDER_THIN);
	style.setTopBorderColor(IndexedColors.BLACK.getIndex());
	style.setBorderBottom(CellStyle.BORDER_THIN);
	style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
	style.setDataFormat(wb.createDataFormat().getFormat("$#,##0"));
	styles.put("cell_number_sub_total", style);




	cellFont = wb.createFont();
	cellFont.setFontName("標楷體");
	cellFont.setFontHeightInPoints((short)14);
	cellFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
	style = wb.createCellStyle();
	style.setFont(cellFont);
	style.setAlignment(CellStyle.ALIGN_CENTER);
	style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
	//2010/05/13 jeff Gu  start   修正    報表excel檔修正 數值過長時欄位展開 問題  
	style.setWrapText(false);//設定文字是否換行
	//2010/05/13 jeff Gu  end   修正    報表excel檔修正 數值過長時欄位展開 問題
	style.setBorderRight(CellStyle.BORDER_THIN);
	style.setRightBorderColor(IndexedColors.BLACK.getIndex());
	style.setBorderLeft(CellStyle.BORDER_THIN);
	style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
	style.setBorderTop(CellStyle.BORDER_THIN);
	style.setTopBorderColor(IndexedColors.BLACK.getIndex());
	style.setBorderBottom(CellStyle.BORDER_THIN);
	style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
	styles.put("cell_total", style);

	style = wb.createCellStyle();
	cellNumberFont = wb.createFont();
	cellNumberFont.setFontName("Arial");
	cellNumberFont.setFontHeightInPoints((short)14);
	cellNumberFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
	style.setFont(cellNumberFont);
	style.setAlignment(CellStyle.ALIGN_RIGHT);
	style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
	//2010/05/13 jeff Gu  start   修正    報表excel檔修正 數值過長時欄位展開 問題  
	style.setWrapText(false);//設定文字是否換行
	//2010/05/13 jeff Gu  end   修正    報表excel檔修正 數值過長時欄位展開 問題
	style.setBorderRight(CellStyle.BORDER_THIN);
	style.setRightBorderColor(IndexedColors.BLACK.getIndex());
	style.setBorderLeft(CellStyle.BORDER_THIN);
	style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
	style.setBorderTop(CellStyle.BORDER_THIN);
	style.setTopBorderColor(IndexedColors.BLACK.getIndex());
	style.setBorderBottom(CellStyle.BORDER_THIN);
	style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
	style.setDataFormat(wb.createDataFormat().getFormat("$#,##0"));
	styles.put("cell_number_total", style);

	return styles;
}


private void writeGroupHtmlData(StringBuffer html, List<Map<String, Object>> channelDatas) {
	if (null != channelDatas && 0 != channelDatas.size())
	{
		boolean first = true;
		for(Map<String, Object> channelData : channelDatas)
		{
			//select   group_pk, GROUP_NAME  ,VIRTUAL_ACCOUNT_NO , ACCOUNT_NO ,
			String groupPk = (String)channelData.get("GROUP_PK");
			// Long groupPk = new Long(String.valueOf(dbRecord.get("group_pk")));
			String groupName = (String)channelData.get("GROUP_NAME");
			String VirtualAccountNo = (String)channelData.get("VIRTUAL_ACCOUNT_NO");
			String AccountNo = (String)channelData.get("ACCOUNT_NO");
			//  String commissionCodeName = (String)dbRecord.get("COMMISSION_CODE_NAME");
			Long recordCount = new Long(String.valueOf(channelData.get("RECORD_COUNT")));
			Long realAmount = new Long(String.valueOf(channelData.get("REAL_AMOUNT")));



			html.append("<tr>").append("\r\n");
			if (first)
			{
				html.append("<td rowspan=\"").append(channelDatas.size()).append("\">").append(groupName).append("</td>").append("\r\n");
				first = false;
			}

			/*   String[] title = {
                String[] titles = {
                        "業者","虛擬帳號" ,"實體帳號", "代收筆數", "入帳金額"
                        , "明細"};*/



			// 虛擬帳號
			html.append("<td>").append(VirtualAccountNo).append("</td>").append("\r\n");
			// 實體帳號
			html.append("<td>").append(AccountNo).append("</td>").append("\r\n");
			// 代收筆數
			html.append("<td align=\"right\">").append(numberFormat.format(recordCount)).append("</td>").append("\r\n");
			// 入帳金額
			html.append("<td align=\"right\">").append(numberFormat.format(realAmount)).append("</td>").append("\r\n");
			// 明細
			html.append("<td align=\"right\">").append("</td>").append("\r\n");

			html.append("</tr>").append("\r\n");
		}

	}
}

public String doExportGroupStatisticsHtml(Group group, Integer qryType, Date dateFrom, Date dateTo)
{
	Map<String, List<Map<String, Object>> > dbData = this.queryForGroupStatisticsList(group, qryType, dateFrom, dateTo);

	StringBuffer html = new StringBuffer();
	//header row

	String[] titles = { "業者","虛擬帳號" ,"實體帳號", "代收筆數", "入帳金額"};
	if (null == dbData || 0 == dbData.size())
	{
		html.append("<div id=\"queryNothingBlockDiv\" style=\"display: none;\">").append("\r\n");
		html.append("查無資料").append("\r\n");
		html.append("</div>").append("\r\n");


		//顯示表頭
		html.append("<table id=\"listTable\">").append("\r\n");
		html.append("<thead><tr>").append("\r\n");


		for (int i = 0; i < titles.length; i++) {
			html.append("<th>").append(titles[i]).append("</th>").append("\r\n");
		}

		html.append("</tr></thead>").append("\r\n");
		html.append("</tbody></table>").append("\r\n");

		html.append("<script type=\"text/javascript\">").append("\r\n");
		html.append("<!--").append("\r\n");
		html.append("$(document).ready(function() {").append("\r\n");
		html.append("$( \"#queryNothingBlockDiv\" ).dialog({  title:'"+UserUtil.getCurrentMenuItemName()+"' ,width: 300, resizable: false, modal: true, buttons : {").append("\r\n");
		html.append("Ok : function() {").append("\r\n");
		html.append("$(this).dialog(\"close\");").append("\r\n");
		html.append("}").append("\r\n");
		html.append("}  });").append("\r\n");
		html.append("});").append("\r\n");
		html.append("//-->").append("\r\n");
		html.append("</script>").append("\r\n");


		return html.toString();
	}

	html.append("<table id=\"listTable\">").append("\r\n");
	html.append("<thead><tr>").append("\r\n");



	for (int i = 0; i < titles.length; i++) {
		html.append("<th>").append(titles[i]).append("</th>").append("\r\n");
	}

	html.append("</tr></thead>").append("\r\n");
	html.append("<tbody>").append("\r\n");

	long recordCountStoreTotal = 0;
	long realAmountStoreTotal = 0;
	String show =group.getGroupPk().toString();
	String dbDataKeys[] = new String[]{show};


	for (String key :dbDataKeys){
		List<Map<String, Object>> channelDatas = dbData.get(key);

		if (null != channelDatas &&  0 != channelDatas.size())
		{
			writeGroupHtmlData(html, channelDatas);

			Map<String, Object> subTotalMap = channelDatas.get(channelDatas.size() - 1); // 於preProcess就有去計算每個通路的小計，並放在最後一筆資料
			recordCountStoreTotal =  recordCountStoreTotal + (Long)subTotalMap.get("RECORD_COUNT");
			realAmountStoreTotal = realAmountStoreTotal + (Long)subTotalMap.get("REAL_AMOUNT");
		}


		// 計算完就free
		if (null != channelDatas)
		{
			channelDatas.clear();
			channelDatas.remove(key);
		}
	}

	/*    String[] titles = {
                        "業者","虛擬帳號" ,"實體帳號", "代收筆數", "入帳金額"
                        , "明細"};*/

	// 合計
	html.append("<tr>").append("\r\n");
	html.append("<td colspan=\"2\">合計").append("</td>").append("\r\n").append("<td></td>");

	//代收筆數
	html.append("<td align=\"right\">").append(numberFormat.format(recordCountStoreTotal)).append("</td>").append("\r\n");

	// 入帳金額
	html.append("<td align=\"right\">").append(numberFormat.format(realAmountStoreTotal)).append("</td>").append("\r\n");

	// 明細
	//html.append("<td align=\"right\">").append("</td>").append("\r\n");
	html.append("</tr>").append("\r\n");


	// 下載按鈕
	html.append("<tr>").append("\r\n");
	html.append("<td colspan=\""+titles.length + "\" align=\"center\">")
	.append("<input type=\"button\" id=\"doDownloadExcelFileBtn\" value=\"下載(Excel)\" onclick=\"javascript:doDownloadExcelFileAction();\" />")
	.append(" <input type=\"button\" id=\"printBtn2\" value=\"列印\"  onclick=\"javascript:divPrint('queryResultDiv');\" />") 
	.append("</td>").append("\r\n");
	html.append("</tr>").append("\r\n");

	html.append("</tbody></table>").append("\r\n");

	return html.toString();
}

private void writeGroupExcelData(Sheet sheet, Map<String, CellStyle> styles, List<Map<String, Object>> channelDatas )
{
	if (null != channelDatas && 0 != channelDatas.size())
	{
		Row dataRow = null;
		Cell cell = null;
		int rownum = sheet.getLastRowNum() + 1;
		String cellRangeAddress = null;
		for(Map<String, Object> channelData : channelDatas)
		{
			//select   group_pk, GROUP_NAME  ,VIRTUAL_ACCOUNT_NO , ACCOUNT_NO ,
			String groupPk = (String)channelData.get("GROUP_PK");
			// Long groupPk = new Long(String.valueOf(dbRecord.get("group_pk")));
			String groupName = (String)channelData.get("GROUP_NAME");
			String VirtualAccountNo = (String)channelData.get("VIRTUAL_ACCOUNT_NO");
			String AccountNo = (String)channelData.get("ACCOUNT_NO");
			//  String commissionCodeName = (String)dbRecord.get("COMMISSION_CODE_NAME");
			Long recordCount = new Long(String.valueOf(channelData.get("RECORD_COUNT")));

			Long realAmount = new Long(String.valueOf(channelData.get("REAL_AMOUNT")));




			dataRow = sheet.createRow(rownum++);
			if (null == cellRangeAddress)
				cellRangeAddress = "$A$"+rownum;
			dataRow.setHeightInPoints((float) 25);



			cell = dataRow.createCell(0); 
			cell.setCellValue(groupName);
			cell.setCellStyle(styles.get("cell"));

			/*
                String[] titles = {
                                "業者","虛擬帳號" ,"實體帳號", "代收筆數", "入帳金額"
                                , "明細"};

			 */


			if("小計".equals(VirtualAccountNo)){

				// 虛擬帳號--小計
				cell = dataRow.createCell(1);
				cell.setCellValue(VirtualAccountNo);
				cell.setCellStyle(styles.get("cell_total"));

				// 實體帳號
				cell = dataRow.createCell(2);
				cell.setCellValue("");
				cell.setCellStyle(styles.get("cell_total"));

				// 代收筆數
				cell = dataRow.createCell(3);
				cell.setCellValue(recordCount);
				cell.setCellStyle(styles.get("cell_total"));



				// 入帳金額
				cell = dataRow.createCell(4);
				cell.setCellValue(realAmount);
				cell.setCellStyle(styles.get("cell_number_total"));



			}else{ // 虛擬帳號
				cell = dataRow.createCell(1);
				cell.setCellValue(VirtualAccountNo);
				cell.setCellStyle(styles.get("cell"));

				// 實體帳號
				cell = dataRow.createCell(2);
				cell.setCellValue(AccountNo);
				cell.setCellStyle(styles.get("cell"));

				// 代收筆數
				cell = dataRow.createCell(3);
				cell.setCellValue(recordCount);
				cell.setCellStyle(styles.get("cell"));


				// 實際入帳金額小計
				cell = dataRow.createCell(4);
				cell.setCellValue(realAmount);
				cell.setCellStyle(styles.get("cell_number"));

			}





		}

		sheet.addMergedRegion(CellRangeAddress.valueOf(cellRangeAddress + ":$A$" + rownum));            
	}
}

public String doExportGroupStatisticsXls(Group group, Integer qryType, Date dateFrom, Date dateTo)
{ 
	Map<String, List<Map<String, Object>>> dbRecords = this.queryForGroupStatisticsList(group, qryType, dateFrom, dateTo);

	// 無統計資料
	if (null == dbRecords || 0 == dbRecords.size())
		return null;

	Map<String, List<Map<String, Object>> > dbData = dbRecords;

	logger.info("dbData {}",dbData);
	logger.info("dbRecords {}",dbRecords);
	SimpleDateFormat dateFormat1 = new SimpleDateFormat("yyyy/MM/dd");
	SimpleDateFormat dateFormat2 = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
	Workbook wb = new HSSFWorkbook(); // 若要產生xlsx則使用 XSSFWorkbook 即可

	Map<String, CellStyle> styles = createStyles(wb);

	Sheet sheet = wb.createSheet("帳單通路統計表");
	PrintSetup printSetup = sheet.getPrintSetup();
	printSetup.setLandscape(true);
	sheet.setFitToPage(true);
	sheet.setHorizontallyCenter(true);
	sheet.setZoom(3, 4);

	// 行數
	int rownum = 0;

	//title row
	Row titleRow = sheet.createRow(rownum++);
	titleRow.setHeightInPoints((float)32.25);
	Cell titleCell = titleRow.createCell(0);
	titleCell.setCellValue("帳單通路統計表");
	titleCell.setCellStyle(styles.get("title"));
	sheet.addMergedRegion(CellRangeAddress.valueOf("$A$1:$E$1"));

	Row blankRow = sheet.createRow(rownum++);
	blankRow.setHeightInPoints((float)18);

	//資料期間
	Row conditionRow1 = sheet.createRow(rownum++);
	conditionRow1.setHeightInPoints((float)24.5);
	Cell conditionCell = conditionRow1.createCell(0);
	conditionCell.setCellValue("資料期間:" + dateFormat1.format(dateFrom)+ "~" +dateFormat1.format(dateTo));
	conditionCell.setCellStyle(styles.get("title2"));
	sheet.addMergedRegion(CellRangeAddress.valueOf("$A$"+rownum+":$E$" + rownum));


	Row conditionRow2 = sheet.createRow(rownum++);
	conditionRow2.setHeightInPoints((float)24.5);        

	Cell printDateCell = conditionRow2.createCell(0);
	printDateCell.setCellValue("製表日期/時間:" + dateFormat2.format(Calendar.getInstance().getTime()));
	printDateCell.setCellStyle(styles.get("title2"));
	sheet.addMergedRegion(CellRangeAddress.valueOf("$A$"+rownum+":$E$" + rownum));


	//header row



	String[] titles = {"業者","虛擬帳號" ,"實體帳號", "代收筆數", "入帳金額"};


	Row headerRow = sheet.createRow(rownum++);
	headerRow.setHeightInPoints((float) 25.5);
	Cell headerCell;
	for (int i = 0; i < titles.length; i++) {
		headerCell = headerRow.createCell(i);
		headerCell.setCellValue(titles[i]);
		headerCell.setCellStyle(styles.get("header"));
	}


	long recordCountStoreTotal = 0;
	long realAmountStoreTotal = 0;




	// 豫張(2)
	List<Map<String, Object>> channelDatas = dbData.get(group.getGroupPk().toString());
	if (null != channelDatas &&  0 != channelDatas.size())
	{

		logger.info("channelDatas{}",channelDatas);
		writeGroupExcelData(sheet, styles, channelDatas);

		Map<String, Object> subTotalMap = channelDatas.get(channelDatas.size() - 1);
		recordCountStoreTotal =  recordCountStoreTotal + (Long)subTotalMap.get("RECORD_COUNT");

		realAmountStoreTotal = realAmountStoreTotal + (Long)subTotalMap.get("REAL_AMOUNT");
	}



	rownum = sheet.getLastRowNum() + 1;
	// 超商合計 底部
	Row dataRow = sheet.createRow(rownum++);
	dataRow.setHeightInPoints((float) 25);
	Cell cell = dataRow.createCell(0);
	cell.setCellValue("合計");
	cell.setCellStyle(styles.get("cell_total"));
	cell = dataRow.createCell(1);
	cell.setCellValue("");
	cell.setCellStyle(styles.get("cell_total"));
	sheet.addMergedRegion(CellRangeAddress.valueOf("$A$"+rownum+":$B$" + rownum));


	/*
        String[] titles = {
                        "業者","虛擬帳號" ,"實體帳號", "代收筆數", "入帳金額"
                        , "明細"};

	 */
	//實體帳號 空白
	cell = dataRow.createCell(2);
	cell.setCellValue("");
	cell.setCellStyle(styles.get("cell_number_total"));
	// sheet.addMergedRegion(CellRangeAddress.valueOf("$E$"+rownum+":$F$" + rownum));

	// 代收筆數
	cell = dataRow.createCell(3);
	cell.setCellValue("");
	cell.setCellStyle(styles.get("cell_total"));
	//  sheet.addMergedRegion(CellRangeAddress.valueOf("$G$"+rownum+":$H$" + rownum));
	// 入帳金額
	cell = dataRow.createCell(4);
	cell.setCellValue(realAmountStoreTotal);
	cell.setCellStyle(styles.get("cell_number_total"));
	//  sheet.addMergedRegion(CellRangeAddress.valueOf("$G$"+rownum+":$H$" + rownum));
	// sheet.addMergedRegion(CellRangeAddress.valueOf("$I$"+rownum+":$J$" + rownum));






	int colCount = 0;
	//2011/05/13 jeff Gu  start   修正    報表excel檔修正 數值過長時欄位展開 問題                
	//titles.length == 欄位數

	while (colCount <= titles.length)
	{
		sheet.autoSizeColumn((short) colCount++,true);//自動調整欄位
	}
	//2011/05/13 jeff Gu  end   修正    報表excel檔修正 數值過長時欄位展開 問題
	// Write the output to a file
	String tempDownloadFilePath = tempDirectory + "/" + UserUtil.getUserGroup().getGroupNo() + "/" + UserUtil.getUserId() + "/";
	File directory = new File(tempDownloadFilePath);             
	if(!directory.exists())
	{
		try {
			FileUtils.forceMkdir(directory);
		}
		catch (IOException e) {
			e.printStackTrace();
		}
	}

	String fileFullName = tempDownloadFilePath + UUID.randomUUID().toString() + "-帳單通路統計表.xls";

	FileOutputStream out;
	try {
		out = new FileOutputStream(fileFullName);
		wb.write(out);
		out.close();
	}
	catch (FileNotFoundException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}
	catch (IOException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}
	return fileFullName;
}


public PaymentDao getPaymentDao() {
	return paymentDao;
}


public void setPaymentDao(PaymentDao paymentDao) {
	this.paymentDao = paymentDao;
}


public RealAccountDao getRealAccountDao() {
	return realAccountDao;
}


public void setRealAccountDao(RealAccountDao realAccountDao) {
	this.realAccountDao = realAccountDao;
}



}

